#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
数据库管理器
负责用户、样本、日志等数据的存储和管理
"""

import sqlite3
import logging
import os
import json
from pathlib import Path
from datetime import datetime
from typing import Dict, List, Optional, Any
from dataclasses import dataclass
from enum import Enum

# 使用绝对导入，避免相对导入问题
try:
    from src.models.models import User, Sample, OperationLog, UserRole, SampleStatus, SampleInfo, Results, ImageInfo, ModeInfo
except ImportError:
    # 如果绝对导入失败，尝试相对导入
    try:
        from ..models.models import User, Sample, OperationLog, UserRole, SampleStatus, SampleInfo, Results, ImageInfo, ModeInfo
    except ImportError:
        # 如果都失败，使用字符串导入（延迟导入）
        User = Sample = OperationLog = UserRole = SampleStatus = SampleInfo = Results = ImageInfo = ModeInfo = None

logger = logging.getLogger(__name__)

class DatabaseManager:
    """数据库管理器"""
    
    def __init__(self, db_path: str = None):
        if db_path is None:
            # 默认使用项目根目录下的data目录
            project_root = Path(__file__).parent.parent.parent
            data_dir = project_root / "data"
            data_dir.mkdir(exist_ok=True)
            db_path = str(data_dir / "sample_management.db")
        
        self.db_path = db_path
        self.init_database()
    
    def init_database(self):
        """初始化数据库"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # 创建用户表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    username TEXT UNIQUE NOT NULL,
                    password_hash TEXT NOT NULL,
                    role TEXT NOT NULL,
                    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    last_login TIMESTAMP
                )
            ''')
            
            # 1) 样本主表 - 存放header类信息
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS lis_samples (
                    id TEXT PRIMARY KEY,
                    
                    -- 设备/协议信息
                    protocol TEXT NOT NULL DEFAULT 'CUSTOM_LIS',
                    machine_model TEXT NOT NULL,
                    machine_id TEXT NOT NULL,
                    
                    -- 样本信息
                    sample_no INTEGER NOT NULL,
                    sample_uid TEXT NOT NULL,
                    position_no TEXT NOT NULL,
                    sample_type TEXT NOT NULL,
                    analysis_datetime DATETIME NOT NULL,
                    receive_time DATETIME(3) NOT NULL,
                    
                    -- 患者信息
                    patient_name TEXT,
                    patient_age INTEGER,
                    patient_gender TEXT,
                    
                    -- 分析模式信息
                    analysis_mode TEXT NOT NULL,
                    mode_name_zh TEXT,
                    mode_desc_zh TEXT,
                    mode_typical_fields_json TEXT,  -- JSON数组格式
                    
                    -- 图片元数据
                    image_filename TEXT,
                    image_path TEXT,
                    image_size_bytes INTEGER,
                    image_data_length_hex INTEGER,
                    image_format TEXT,
                    image_saved_flag BOOLEAN,
                    
                    -- 原始报文
                    raw_payload TEXT NOT NULL,
                    
                    -- 维护字段
                    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    
                    -- 状态字段
                    status TEXT DEFAULT 'PENDING'
                )
            ''')
            
            # 2) 通用检验结果表（Key-Value结构）
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS lis_sample_results (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    sample_id TEXT NOT NULL,
                    analyte_code TEXT NOT NULL,
                    value_num DECIMAL(10,4) NOT NULL,
                    unit TEXT NOT NULL,
                    raw_value TEXT NOT NULL,
                    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (sample_id) REFERENCES lis_samples(id) ON DELETE CASCADE
                )
            ''')
            
            # 3) 峰图数据表（TIME/AREA）
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS lis_sample_peaks (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    sample_id TEXT NOT NULL,
                    peak_index INTEGER NOT NULL,
                    time_sec DECIMAL(10,2),
                    area DECIMAL(18,4),
                    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (sample_id) REFERENCES lis_samples(id) ON DELETE CASCADE
                )
            ''')
            
            # 创建索引
            cursor.execute('''
                CREATE INDEX IF NOT EXISTS idx_lis_samples_machine_sample 
                ON lis_samples(machine_id, sample_uid)
            ''')
            
            cursor.execute('''
                CREATE INDEX IF NOT EXISTS idx_lis_samples_time 
                ON lis_samples(analysis_datetime)
            ''')
            
            cursor.execute('''
                CREATE INDEX IF NOT EXISTS idx_lis_samples_machine_time 
                ON lis_samples(machine_id, analysis_datetime)
            ''')
            
            cursor.execute('''
                CREATE INDEX IF NOT EXISTS idx_lis_sample_results_analyte 
                ON lis_sample_results(analyte_code)
            ''')
            
            cursor.execute('''
                CREATE INDEX IF NOT EXISTS idx_lis_sample_peaks_sample 
                ON lis_sample_peaks(sample_id)
            ''')
            
            # 创建操作日志表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS operation_logs (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    operation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    operator TEXT NOT NULL,
                    operation_type TEXT NOT NULL,
                    operation_content TEXT,
                    ip_address TEXT
                )
            ''')
            
            # 创建系统配置表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS system_config (
                    key TEXT PRIMARY KEY,
                    value TEXT,
                    updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # 创建检测结果警告区间表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS test_warning_ranges (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    test_name TEXT NOT NULL,
                    min_value REAL,
                    max_value REAL,
                    unit TEXT,
                    warning_color TEXT DEFAULT 'red',
                    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # 创建备份配置表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS backup_config (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    auto_backup BOOLEAN DEFAULT 1,
                    auto_backup_interval INTEGER DEFAULT 24,
                    close_backup BOOLEAN DEFAULT 1,
                    encryption_type TEXT DEFAULT 'AES',
                    max_backup_files INTEGER DEFAULT 30,
                    backup_database BOOLEAN DEFAULT 1,
                    backup_config BOOLEAN DEFAULT 1,
                    backup_logs BOOLEAN DEFAULT 1,
                    backup_path TEXT,
                    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # 创建备份日志表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS backup_logs (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    backup_name TEXT NOT NULL,
                    backup_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    backup_path TEXT,
                    file_size INTEGER,
                    encryption_type TEXT,
                    backup_type TEXT,
                    status TEXT,
                    message TEXT,
                    operator TEXT,
                    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # 创建图片数据表（用于存储大型图片数据）
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS sample_images (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    sample_id TEXT NOT NULL,
                    filename TEXT,
                    hex_data TEXT,     -- 十六进制数据
                    base64_data TEXT,  -- Base64数据
                    file_size INTEGER,
                    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (sample_id) REFERENCES lis_samples (id) ON DELETE CASCADE
                )
            ''')
            
            # 创建加密密钥表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS encryption_keys (
                    key_name TEXT PRIMARY KEY,
                    public_key TEXT,
                    private_key TEXT,
                    key_type TEXT,
                    key_size INTEGER,
                    description TEXT,
                    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            conn.commit()
            logger.info("数据库初始化完成")
    
    def create_admin_user(self, username: str, password: str) -> bool:
        """创建管理员用户"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute(
                    "INSERT INTO users (username, password_hash, role) VALUES (?, ?, ?)",
                    (username, self._hash_password(password), UserRole.ADMIN.value)
                )
                conn.commit()
                return True
        except sqlite3.IntegrityError:
            return False
    
    def verify_user(self, username: str, password: str) -> Optional[User]:
        """验证用户登录"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute(
                "SELECT * FROM users WHERE username = ? AND password_hash = ?",
                (username, self._hash_password(password))
            )
            result = cursor.fetchone()
            
            if result:
                # 更新最后登录时间
                cursor.execute(
                    "UPDATE users SET last_login = CURRENT_TIMESTAMP WHERE id = ?",
                    (result[0],)
                )
                conn.commit()
                
                return User(
                    id=result[0],
                    username=result[1],
                    password_hash=result[2],
                    role=UserRole(result[3]),
                    created_time=datetime.fromisoformat(result[4]),
                    last_login=datetime.fromisoformat(result[5]) if result[5] else None
                )
            return None
    
    def _hash_password(self, password: str) -> str:
        """简单的密码哈希（实际应用中应使用更安全的方法）"""
        import hashlib
        return hashlib.sha256(password.encode()).hexdigest()
    
    def _parse_sample_status(self, status_str: str) -> SampleStatus:
        """解析样本状态字符串为枚举值"""
        if not status_str:
            return SampleStatus.PENDING
        
        # 映射数据库中的状态字符串到枚举值
        status_mapping = {
            'PENDING': SampleStatus.PENDING,
            '待处理': SampleStatus.PENDING,
            'PROCESSED': SampleStatus.PROCESSED,
            '已处理': SampleStatus.PROCESSED,
            'EXPORTED': SampleStatus.EXPORTED,
            '已导出': SampleStatus.EXPORTED,
            'SENT': SampleStatus.SENT,
            '已发送': SampleStatus.SENT
        }
        
        return status_mapping.get(status_str, SampleStatus.PENDING)
    
    def get_samples(self) -> List[Sample]:
        """获取所有样本 - 支持新的三表结构"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 获取主表数据
                cursor.execute('''
                    SELECT s.*, si.hex_data, si.base64_data
                    FROM lis_samples s
                    LEFT JOIN sample_images si ON s.id = si.sample_id
                    ORDER BY s.receive_time DESC
                ''')
                
                rows = cursor.fetchall()
                samples = []
                
                for row in rows:
                    try:
                        # 解析主表数据
                        sample_id = row[0]
                        protocol = row[1]
                        machine_model = row[2]
                        machine_id = row[3]
                        sample_no = row[4]
                        sample_uid = row[5]
                        position_no = row[6]
                        sample_type = row[7]
                        analysis_datetime = row[8]
                        receive_time = row[9]
                        patient_name = row[10]
                        patient_age = row[11]
                        patient_gender = row[12]
                        analysis_mode = row[13]
                        mode_name_zh = row[14]
                        mode_desc_zh = row[15]
                        mode_typical_fields_json = row[16]
                        image_filename = row[17]
                        image_path = row[18]
                        image_size_bytes = row[19]
                        image_data_length_hex = row[20]
                        image_format = row[21]
                        image_saved_flag = row[22]
                        raw_payload = row[23]
                        created_at = row[24]
                        updated_at = row[25]
                        status = row[26]
                        hex_data = row[27]
                        base64_data = row[28]
                        
                        # 获取检测结果数据
                        cursor.execute('''
                            SELECT analyte_code, value_num, unit, raw_value
                            FROM lis_sample_results
                            WHERE sample_id = ?
                            ORDER BY analyte_code
                        ''', (sample_id,))
                        
                        results_data = {}
                        for result_row in cursor.fetchall():
                            analyte_code = result_row[0]
                            value_num = result_row[1]
                            unit = result_row[2]
                            raw_value = result_row[3]
                            results_data[analyte_code] = raw_value
                        
                        # 获取峰图数据
                        cursor.execute('''
                            SELECT peak_index, time_sec, area
                            FROM lis_sample_peaks
                            WHERE sample_id = ?
                            ORDER BY peak_index
                        ''', (sample_id,))
                        
                        time_data = []
                        area_data = []
                        peak_rows = cursor.fetchall()
                        
                        if peak_rows:
                            # 获取最大峰索引
                            max_peak_index = max(row[0] for row in peak_rows)
                            
                            # 初始化数组
                            time_data = ["00.00"] * max_peak_index
                            area_data = ["00.00"] * max_peak_index
                            
                            # 填充实际数据
                            for peak_row in peak_rows:
                                peak_index = peak_row[0]
                                time_sec = peak_row[1]
                                area = peak_row[2]
                                
                                if time_sec is not None:
                                    time_data[peak_index - 1] = f"{time_sec:.2f}"
                                if area is not None:
                                    area_data[peak_index - 1] = f"{area:.4f}"
                        
                        # 创建SampleInfo对象
                        sample_info = SampleInfo(
                            sample_id=sample_uid,
                            sample_number=str(sample_no),  # 使用sample_no作为样本编号
                            sample_uid=sample_uid,  # 设置仪器检测编号
                            patient_name=patient_name or "",
                            patient_age=patient_age or 0,
                            patient_gender=patient_gender or "",
                            datetime=analysis_datetime,
                            sample_type=sample_type,
                            sample_type_name="",
                            sample_category="",
                            report_id="",
                            position=position_no
                        )
                        
                        # 创建Results对象
                        results = Results(
                            HbA1a=results_data.get('HbA1a', ''),
                            HbA1b=results_data.get('HbA1b', ''),
                            HbF=results_data.get('HbF', ''),
                            LA1c=results_data.get('LA1c', ''),
                            HbA1c=results_data.get('HbA1c', ''),
                            HbA0=results_data.get('HbA0', ''),
                            eAG=results_data.get('eAG', ''),
                            A2=results_data.get('A2', ''),
                            HbE=results_data.get('HbE', ''),
                            HbD=results_data.get('HbD', ''),
                            HbS=results_data.get('HbS', ''),
                            HbC=results_data.get('HbC', ''),
                            p3=results_data.get('p3', ''),
                            v_win=results_data.get('v_win', ''),
                            TIME=time_data,
                            AREA=area_data,
                            TIME_DETAILS={},
                            AREA_DETAILS={},
                            other_results=results_data
                        )
                        
                        # 创建ImageInfo对象
                        image = ImageInfo(
                            filename=image_filename or "",
                            size=image_size_bytes or 0,
                            path=image_path or "",
                            saved=bool(image_saved_flag),
                            hex_data=hex_data or "",
                            base64_data=base64_data or "",
                            error=""
                        )
                        
                        # 创建ModeInfo对象
                        typical_fields = []
                        if mode_typical_fields_json:
                            try:
                                typical_fields = json.loads(mode_typical_fields_json)
                            except:
                                typical_fields = []
                        
                        mode_info = ModeInfo(
                            name=mode_name_zh or "",
                            description=mode_desc_zh or "",
                            mode_name=mode_name_zh or "",
                            mode_description=mode_desc_zh or "",
                            test_count=len(typical_fields),
                            test_items=typical_fields,
                            typical_fields=typical_fields
                        )
                        
                        # 创建Sample对象
                        sample = Sample(
                            id=sample_id,
                            protocol=protocol,
                            machine_model=machine_model,
                            machine_id=machine_id,
                            sample_info=sample_info,
                            results=results,
                            image=image,
                            analysis_mode=analysis_mode,
                            mode_info=mode_info,
                            raw=raw_payload,
                            receive_time=datetime.fromisoformat(receive_time) if receive_time else datetime.now(),
                            status=self._parse_sample_status(status),
                            created_time=datetime.fromisoformat(created_at) if created_at else datetime.now(),
                            updated_time=datetime.fromisoformat(updated_at) if updated_at else datetime.now()
                        )
                        samples.append(sample)
                        
                    except Exception as e:
                        logger.error(f"解析样本数据失败: {e}, row: {row}")
                        continue
                
                return samples
                
        except Exception as e:
            logger.error(f"获取样本列表失败: {e}")
            return []

    def add_sample(self, sample: Sample) -> bool:
        """添加样本 - 兼容旧版本接口"""
        try:
            # 将Sample对象转换为JSON格式
            json_data = {
                "protocol": sample.protocol,
                "machine_model": sample.machine_model,
                "machine_id": sample.machine_id,
                "sample_info": {
                    "datetime": sample.sample_info.datetime,
                    "sample_no": sample.sample_info.sample_number,
                    "sample_id": sample.sample_info.sample_id,
                    "position": sample.sample_info.position,
                    "sample_type": sample.sample_info.sample_type,
                    "patient_name": sample.sample_info.patient_name,
                    "patient_age": sample.sample_info.patient_age,
                    "patient_gender": sample.sample_info.patient_gender
                },
                "results": {
                    "HbA1a": sample.results.HbA1a,
                    "HbA1b": sample.results.HbA1b,
                    "HbF": sample.results.HbF,
                    "LA1c": sample.results.LA1c,
                    "HbA1c": sample.results.HbA1c,
                    "HbA0": sample.results.HbA0,
                    "eAG": sample.results.eAG,
                    "A2": sample.results.A2,
                    "HbE": sample.results.HbE,
                    "HbD": sample.results.HbD,
                    "HbS": sample.results.HbS,
                    "HbC": sample.results.HbC,
                    "p3": sample.results.p3,
                    "v_win": sample.results.v_win,
                    "TIME": sample.results.TIME,
                    "AREA": sample.results.AREA
                },
                "image": {
                    "filename": sample.image.filename,
                    "size": sample.image.size,
                    "path": sample.image.path,
                    "saved": sample.image.saved
                },
                "analysis_mode": sample.analysis_mode.value if hasattr(sample.analysis_mode, 'value') else str(sample.analysis_mode),
                "mode_info": {
                    "name": sample.mode_info.name,
                    "description": sample.mode_info.description,
                    "typical_fields": sample.mode_info.typical_fields
                },
                "raw": sample.raw,
                "receive_time": sample.receive_time.isoformat()
            }
            
            # 调用新的入库方法（已重命名为通用方法）
            return self.add_sample_from_data(json_data)
            
        except Exception as e:
            logger.error(f"添加样本失败: {e}")
            return False
    
    def update_sample(self, sample: Sample) -> bool:
        """更新样本 - 兼容旧版本接口"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 首先删除旧的检测结果和峰图数据
                cursor.execute('DELETE FROM lis_sample_results WHERE sample_id = ?', (sample.id,))
                cursor.execute('DELETE FROM lis_sample_peaks WHERE sample_id = ?', (sample.id,))
                
                # 更新主表数据
                cursor.execute('''
                    UPDATE lis_samples SET
                        protocol = ?, machine_model = ?, machine_id = ?,
                        sample_no = ?, sample_uid = ?, position_no = ?, sample_type = ?, analysis_datetime = ?,
                        patient_name = ?, patient_age = ?, patient_gender = ?,
                        analysis_mode = ?, mode_name_zh = ?, mode_desc_zh = ?, mode_typical_fields_json = ?,
                        image_filename = ?, image_path = ?, image_size_bytes = ?, image_data_length_hex = ?, 
                        image_format = ?, image_saved_flag = ?, raw_payload = ?, updated_at = CURRENT_TIMESTAMP
                    WHERE id = ?
                ''', (
                    sample.protocol, sample.machine_model, sample.machine_id,
                    int(sample.sample_info.sample_number) if sample.sample_info.sample_number.isdigit() else 0,
                    sample.sample_info.sample_number, sample.sample_info.position, sample.sample_info.sample_type,
                    sample.sample_info.datetime, 
                    sample.sample_info.patient_name, sample.sample_info.patient_age, sample.sample_info.patient_gender,
                    sample.analysis_mode.value if hasattr(sample.analysis_mode, 'value') else str(sample.analysis_mode), 
                    sample.mode_info.name_zh if hasattr(sample.mode_info, 'name_zh') else sample.mode_info.name,
                    sample.mode_info.desc_zh if hasattr(sample.mode_info, 'desc_zh') else sample.mode_info.description,
                    json.dumps(sample.mode_info.typical_fields, ensure_ascii=False),
                    sample.image.filename, sample.image.path, sample.image.size, 0,
                    '', sample.image.saved, sample.raw, sample.id
                ))
                
                # 重新插入检测结果数据
                analyte_units = {
                    'HbA1a': '%', 'HbA1b': '%', 'HbF': '%', 'LA1c': '%', 'HbA1c': '%', 
                    'HbA0': '%', 'p3': '%', 'A2': '%', 'HbE': '%', 'HbD': '%', 
                    'HbS': '%', 'HbC': '%', 'eAG': 'mg/dL'
                }
                
                results_data = {
                    'HbA1a': sample.results.HbA1a,
                    'HbA1b': sample.results.HbA1b,
                    'HbF': sample.results.HbF,
                    'LA1c': sample.results.LA1c,
                    'HbA1c': sample.results.HbA1c,
                    'HbA0': sample.results.HbA0,
                    'eAG': sample.results.eAG,
                    'A2': sample.results.A2,
                    'HbE': sample.results.HbE,
                    'HbD': sample.results.HbD,
                    'HbS': sample.results.HbS,
                    'HbC': sample.results.HbC,
                    'p3': sample.results.p3,
                    'v_win': sample.results.v_win
                }
                
                for analyte_code, raw_value in results_data.items():
                    # 保存所有数据，包括"00.00"和空值
                    if raw_value is not None:  # 只检查是否为None，不排除"00.00"
                        try:
                            # 尝试转换为数值，如果失败则使用0.0
                            if raw_value and raw_value != "":
                                value_num = float(raw_value)
                            else:
                                value_num = 0.0
                            
                            unit = analyte_units.get(analyte_code, '%')
                            
                            cursor.execute('''
                                INSERT INTO lis_sample_results (sample_id, analyte_code, value_num, unit, raw_value)
                                VALUES (?, ?, ?, ?, ?)
                            ''', (sample.id, analyte_code, value_num, unit, raw_value))
                        except (ValueError, TypeError):
                            # 如果转换失败，仍然保存原始值
                            logger.warning(f"无法解析检测结果: {analyte_code}={raw_value}，保存原始值")
                            cursor.execute('''
                                INSERT INTO lis_sample_results (sample_id, analyte_code, value_num, unit, raw_value)
                                VALUES (?, ?, ?, ?, ?)
                            ''', (sample.id, analyte_code, 0.0, analyte_units.get(analyte_code, '%'), raw_value))
                
                # 重新插入峰图数据
                time_array = sample.results.TIME
                area_array = sample.results.AREA
                
                # 取最大长度
                max_length = max(len(time_array), len(area_array))
                
                for i in range(max_length):
                    peak_index = i + 1
                    time_sec = None
                    area = None
                    
                    # 保存TIME数据，包括"00.00"
                    if i < len(time_array) and time_array[i] is not None:
                        try:
                            if time_array[i] and time_array[i] != "":
                                time_sec = float(time_array[i])
                            else:
                                time_sec = 0.0
                        except (ValueError, TypeError):
                            # 如果转换失败，保存为0.0
                            time_sec = 0.0
                    
                    # 保存AREA数据，包括"00.00"
                    if i < len(area_array) and area_array[i] is not None:
                        try:
                            if area_array[i] and area_array[i] != "":
                                area = float(area_array[i])
                            else:
                                area = 0.0
                        except (ValueError, TypeError):
                            # 如果转换失败，保存为0.0
                            area = 0.0
                    
                    cursor.execute('''
                        INSERT INTO lis_sample_peaks (sample_id, peak_index, time_sec, area)
                        VALUES (?, ?, ?, ?)
                    ''', (sample.id, peak_index, time_sec, area))
                
                conn.commit()
                logger.info(f"成功更新样本: {sample.id}")
                return True
                
        except Exception as e:
            logger.error(f"更新样本失败: {e}")
            return False
    
    def add_sample_from_data(self, json_data: Dict[str, Any]) -> bool:
        """从通信数据添加样本 - 支持新的三表结构（通用方法）"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 解析JSON数据
                protocol = json_data.get('protocol', 'CUSTOM_LIS')
                machine_model = json_data.get('machine_model', '')
                machine_id = json_data.get('machine_id', '')
                
                # 解析样本信息
                sample_info = json_data.get('sample_info', {})
                sample_no = int(sample_info.get('sample_no', 0))
                sample_uid = sample_info.get('sample_id', '')
                position_no = sample_info.get('position', '')
                sample_type = sample_info.get('sample_type', '')
                analysis_datetime = sample_info.get('datetime', '')
                
                # 解析患者信息
                patient_name = sample_info.get('patient_name', '')
                patient_age = sample_info.get('patient_age', None)
                patient_gender = sample_info.get('patient_gender', '')
                
                # 解析分析模式信息
                analysis_mode = json_data.get('analysis_mode', '')
                mode_info = json_data.get('mode_info', {})
                mode_name_zh = mode_info.get('name', '')
                mode_desc_zh = mode_info.get('description', '')
                typical_fields = mode_info.get('typical_fields', [])
                mode_typical_fields_json = json.dumps(typical_fields, ensure_ascii=False)
                
                # 解析图片信息
                image_info = json_data.get('image', {})
                image_filename = image_info.get('filename', '')
                image_path = image_info.get('path', '')
                image_size_bytes = image_info.get('size', 0)
                image_data_length_hex = image_info.get('data_length', 0)
                image_format = image_info.get('format', '')
                image_saved_flag = image_info.get('saved', False)
                
                # 原始报文和接收时间
                raw_payload = json_data.get('raw', '')
                receive_time = json_data.get('receive_time', datetime.now().isoformat())
                
                # 生成样本ID
                sample_id = f"SAMPLE_{int(datetime.now().timestamp() * 1000)}_{hash(sample_uid) % 10000:04x}"
                
                # 1. 插入主表数据
                cursor.execute('''
                    INSERT INTO lis_samples (
                        id, protocol, machine_model, machine_id,
                        sample_no, sample_uid, position_no, sample_type, analysis_datetime, receive_time,
                        patient_name, patient_age, patient_gender,
                        analysis_mode, mode_name_zh, mode_desc_zh, mode_typical_fields_json,
                        image_filename, image_path, image_size_bytes, image_data_length_hex, image_format, image_saved_flag,
                        raw_payload, created_at, updated_at, status
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                ''', (
                    sample_id, protocol, machine_model, machine_id,
                    sample_no, sample_uid, position_no, sample_type, analysis_datetime, receive_time,
                    patient_name, patient_age, patient_gender,
                    analysis_mode, mode_name_zh, mode_desc_zh, mode_typical_fields_json,
                    image_filename, image_path, image_size_bytes, image_data_length_hex, image_format, image_saved_flag,
                    raw_payload, datetime.now().isoformat(), datetime.now().isoformat(), 'PENDING'
                ))
                
                # 2. 插入检测结果数据
                results = json_data.get('results', {})
                
                # 定义检测项目及其单位
                analyte_units = {
                    'HbA1a': '%', 'HbA1b': '%', 'HbF': '%', 'LA1c': '%', 'HbA1c': '%', 
                    'HbA0': '%', 'p3': '%', 'A2': '%', 'HbE': '%', 'HbD': '%', 
                    'HbS': '%', 'HbC': '%', 'eAG': 'mg/dL'  # eAG单位需要根据实际情况调整
                }
                
                for analyte_code, raw_value in results.items():
                    # 跳过数组字段
                    if analyte_code in ['TIME', 'AREA', 'TIME_DETAILS', 'AREA_DETAILS']:
                        continue
                    
                    # 保存所有数据，包括"00.00"和空值
                    if raw_value is not None:  # 只检查是否为None，不排除"00.00"
                        try:
                            # 保持原始精度，不进行数值转换
                            if raw_value and raw_value != "":
                                # 对于数值型数据，保持原始精度
                                if raw_value.replace('.', '').replace('-', '').isdigit() or raw_value.replace('.', '').replace('-', '').replace('0', '').replace('.', '') == '':
                                    value_num = float(raw_value)
                                else:
                                    # 非数值型数据，保存为0.0但保留原始值
                                    value_num = 0.0
                            else:
                                value_num = 0.0
                            
                            unit = analyte_units.get(analyte_code, '%')
                            
                            cursor.execute('''
                                INSERT INTO lis_sample_results (sample_id, analyte_code, value_num, unit, raw_value)
                                VALUES (?, ?, ?, ?, ?)
                            ''', (sample_id, analyte_code, value_num, unit, raw_value))
                        except (ValueError, TypeError):
                            # 如果转换失败，仍然保存原始值
                            logger.warning(f"无法解析检测结果: {analyte_code}={raw_value}，保存原始值")
                            cursor.execute('''
                                INSERT INTO lis_sample_results (sample_id, analyte_code, value_num, unit, raw_value)
                                VALUES (?, ?, ?, ?, ?)
                            ''', (sample_id, analyte_code, 0.0, analyte_units.get(analyte_code, '%'), raw_value))
                
                # 3. 插入峰图数据
                time_array = results.get('TIME', [])
                area_array = results.get('AREA', [])
                
                # 取最大长度
                max_length = max(len(time_array), len(area_array))
                
                for i in range(max_length):
                    peak_index = i + 1
                    time_sec = None
                    area = None
                    
                    # 保存TIME数据，包括"00.00"
                    if i < len(time_array) and time_array[i] is not None:
                        try:
                            if time_array[i] and time_array[i] != "":
                                time_sec = float(time_array[i])
                            else:
                                time_sec = 0.0
                        except (ValueError, TypeError):
                            # 如果转换失败，保存为0.0
                            time_sec = 0.0
                    
                    # 保存AREA数据，包括"00.00"
                    if i < len(area_array) and area_array[i] is not None:
                        try:
                            if area_array[i] and area_array[i] != "":
                                area = float(area_array[i])
                            else:
                                area = 0.0
                        except (ValueError, TypeError):
                            # 如果转换失败，保存为0.0
                            area = 0.0
                    
                    cursor.execute('''
                        INSERT INTO lis_sample_peaks (sample_id, peak_index, time_sec, area)
                        VALUES (?, ?, ?, ?)
                    ''', (sample_id, peak_index, time_sec, area))
                
                # 4. 如果有图片数据，插入图片表
                if image_filename and (image_info.get('hex_data') or image_info.get('base64_data')):
                    cursor.execute('''
                        INSERT INTO sample_images (sample_id, filename, hex_data, base64_data, file_size)
                        VALUES (?, ?, ?, ?, ?)
                ''', (
                        sample_id, 
                        image_filename,
                        image_info.get('hex_data', ''),
                        image_info.get('base64_data', ''),
                        image_size_bytes
                    ))
                
                conn.commit()
                logger.info(f"成功添加样本: {sample_id}")
                return True
                
        except Exception as e:
            logger.error(f"添加样本失败: {e}")
            return False
    
    def search_samples(self, search_term: str) -> List[Sample]:
        """搜索样本（简单搜索）"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT s.*, si.hex_data, si.base64_data
                    FROM lis_samples s
                    LEFT JOIN sample_images si ON s.id = si.sample_id
                    WHERE s.sample_uid LIKE ? OR s.machine_id LIKE ? OR s.sample_no LIKE ? OR s.patient_name LIKE ?
                    ORDER BY s.receive_time DESC
                ''', (f'%{search_term}%', f'%{search_term}%', f'%{search_term}%', f'%{search_term}%'))
                
                rows = cursor.fetchall()
                samples = []
                
                for row in rows:
                    try:
                        # 解析主表数据
                        sample_id = row[0]
                        protocol = row[1]
                        machine_model = row[2]
                        machine_id = row[3]
                        sample_no = row[4]
                        sample_uid = row[5]
                        position_no = row[6]
                        sample_type = row[7]
                        analysis_datetime = row[8]
                        receive_time = row[9]
                        patient_name = row[10]
                        patient_age = row[11]
                        patient_gender = row[12]
                        analysis_mode = row[13]
                        mode_name_zh = row[14]
                        mode_desc_zh = row[15]
                        mode_typical_fields_json = row[16]
                        image_filename = row[17]
                        image_path = row[18]
                        image_size_bytes = row[19]
                        image_data_length_hex = row[20]
                        image_format = row[21]
                        image_saved_flag = row[22]
                        raw_payload = row[23]
                        created_at = row[24]
                        updated_at = row[25]
                        status = row[26]
                        hex_data = row[27]
                        base64_data = row[28]
                        
                        # 获取检测结果数据
                        cursor.execute('''
                            SELECT analyte_code, value_num, unit, raw_value
                            FROM lis_sample_results
                            WHERE sample_id = ?
                            ORDER BY analyte_code
                        ''', (sample_id,))
                        
                        results_data = {}
                        for result_row in cursor.fetchall():
                            analyte_code = result_row[0]
                            value_num = result_row[1]
                            unit = result_row[2]
                            raw_value = result_row[3]
                            results_data[analyte_code] = raw_value
                        
                        # 获取峰图数据
                        cursor.execute('''
                            SELECT peak_index, time_sec, area
                            FROM lis_sample_peaks
                            WHERE sample_id = ?
                            ORDER BY peak_index
                        ''', (sample_id,))
                        
                        time_data = []
                        area_data = []
                        peak_rows = cursor.fetchall()
                        
                        if peak_rows:
                            # 获取最大峰索引
                            max_peak_index = max(row[0] for row in peak_rows)
                            
                            # 初始化数组
                            time_data = ["00.00"] * max_peak_index
                            area_data = ["00.00"] * max_peak_index
                            
                            # 填充实际数据
                            for peak_row in peak_rows:
                                peak_index = peak_row[0]
                                time_sec = peak_row[1]
                                area = peak_row[2]
                                
                                if time_sec is not None:
                                    time_data[peak_index - 1] = f"{time_sec:.2f}"
                                if area is not None:
                                    area_data[peak_index - 1] = f"{area:.4f}"
                        
                        # 创建SampleInfo对象
                        sample_info = SampleInfo(
                            sample_id=sample_uid,
                            sample_number=str(sample_no),  # 使用sample_no作为样本编号
                            sample_uid=sample_uid,  # 设置仪器检测编号
                            patient_name=patient_name or "",
                            patient_age=patient_age or 0,
                            patient_gender=patient_gender or "",
                            datetime=analysis_datetime,
                            sample_type=sample_type,
                            sample_type_name="",
                            sample_category="",
                            report_id="",
                            position=position_no
                        )
                        
                        # 创建Results对象
                        results = Results(
                            HbA1a=results_data.get('HbA1a', ''),
                            HbA1b=results_data.get('HbA1b', ''),
                            HbF=results_data.get('HbF', ''),
                            LA1c=results_data.get('LA1c', ''),
                            HbA1c=results_data.get('HbA1c', ''),
                            HbA0=results_data.get('HbA0', ''),
                            eAG=results_data.get('eAG', ''),
                            A2=results_data.get('A2', ''),
                            HbE=results_data.get('HbE', ''),
                            HbD=results_data.get('HbD', ''),
                            HbS=results_data.get('HbS', ''),
                            HbC=results_data.get('HbC', ''),
                            p3=results_data.get('p3', ''),
                            v_win=results_data.get('v_win', ''),
                            TIME=time_data,
                            AREA=area_data,
                            TIME_DETAILS={},
                            AREA_DETAILS={},
                            other_results=results_data
                        )
                        
                        # 创建ImageInfo对象
                        image = ImageInfo(
                            filename=image_filename or "",
                            size=image_size_bytes or 0,
                            path=image_path or "",
                            saved=bool(image_saved_flag),
                            hex_data=hex_data or "",
                            base64_data=base64_data or "",
                            error=""
                        )
                        
                        # 创建ModeInfo对象
                        typical_fields = []
                        if mode_typical_fields_json:
                            try:
                                typical_fields = json.loads(mode_typical_fields_json)
                            except:
                                typical_fields = []
                        
                        mode_info = ModeInfo(
                            name=mode_name_zh or "",
                            description=mode_desc_zh or "",
                            mode_name=mode_name_zh or "",
                            mode_description=mode_desc_zh or "",
                            test_count=len(typical_fields),
                            test_items=typical_fields,
                            typical_fields=typical_fields
                        )
                        
                        # 创建Sample对象
                        sample = Sample(
                            id=sample_id,
                            protocol=protocol,
                            machine_model=machine_model,
                            machine_id=machine_id,
                            sample_info=sample_info,
                            results=results,
                            image=image,
                            analysis_mode=analysis_mode,
                            mode_info=mode_info,
                            raw=raw_payload,
                            receive_time=datetime.fromisoformat(receive_time) if receive_time else datetime.now(),
                            status=self._parse_sample_status(status),
                            created_time=datetime.fromisoformat(created_at) if created_at else datetime.now(),
                            updated_time=datetime.fromisoformat(updated_at) if updated_at else datetime.now()
                        )
                        samples.append(sample)
                        
                    except Exception as e:
                        logger.error(f"解析样本数据失败: {e}, row: {row}")
                        continue
                
                return samples
                
        except Exception as e:
            logger.error(f"搜索样本失败: {e}")
            return []
    
    def advanced_search_samples(self, conditions: Dict[str, Any]) -> List[Sample]:
        """高级搜索样本"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 构建查询条件
                where_conditions = []
                params = []
                
                # 样本编号/ID搜索
                if 'sample_number' in conditions:
                    where_conditions.append("(s.sample_uid LIKE ? OR s.sample_no LIKE ?)")
                    search_term = f"%{conditions['sample_number']}%"
                    params.extend([search_term, search_term])
                
                # 患者姓名搜索
                if 'patient_name' in conditions:
                    where_conditions.append("s.patient_name LIKE ?")
                    params.append(f"%{conditions['patient_name']}%")
                
                # 性别搜索
                if 'patient_gender' in conditions:
                    where_conditions.append("s.patient_gender = ?")
                    params.append(conditions['patient_gender'])
                
                # 年龄区间搜索
                if 'age_range' in conditions:
                    age_min, age_max = conditions['age_range']
                    where_conditions.append("s.patient_age BETWEEN ? AND ?")
                    params.extend([age_min, age_max])
                
                # 时间区间搜索
                if 'time_range' in conditions:
                    start_time, end_time = conditions['time_range']
                    where_conditions.append("s.receive_time BETWEEN ? AND ?")
                    params.extend([start_time.isoformat(), end_time.isoformat()])
                
                # 仪器型号搜索
                if 'machine_model' in conditions:
                    where_conditions.append("s.machine_model LIKE ?")
                    params.append(f"%{conditions['machine_model']}%")
                
                # 仪器编号搜索
                if 'machine_id' in conditions:
                    where_conditions.append("s.machine_id LIKE ?")
                    params.append(f"%{conditions['machine_id']}%")
                
                # 分析模式搜索
                if 'analysis_mode' in conditions:
                    where_conditions.append("s.analysis_mode LIKE ?")
                    params.append(f"%{conditions['analysis_mode']}%")
                
                # 协议类型搜索
                if 'protocol' in conditions:
                    where_conditions.append("s.protocol LIKE ?")
                    params.append(f"%{conditions['protocol']}%")
                
                # 状态搜索
                if 'status' in conditions:
                    status_map = {
                        "待处理": "PENDING",
                        "已处理": "PROCESSED", 
                        "已发送": "SENT"
                    }
                    status_value = status_map.get(conditions['status'], conditions['status'])
                    where_conditions.append("s.status = ?")
                    params.append(status_value)
                
                # 构建完整的SQL查询
                sql = '''
                    SELECT s.*, si.hex_data, si.base64_data
                    FROM lis_samples s
                    LEFT JOIN sample_images si ON s.id = si.sample_id
                '''
                
                if where_conditions:
                    sql += " WHERE " + " AND ".join(where_conditions)
                
                sql += " ORDER BY s.receive_time DESC"
                
                logger.info(f"高级搜索SQL: {sql}")
                logger.info(f"搜索参数: {params}")
                
                cursor.execute(sql, params)
                rows = cursor.fetchall()
                samples = []
                
                for row in rows:
                    try:
                        # 解析主表数据
                        sample_id = row[0]
                        protocol = row[1]
                        machine_model = row[2]
                        machine_id = row[3]
                        sample_no = row[4]
                        sample_uid = row[5]
                        position_no = row[6]
                        sample_type = row[7]
                        analysis_datetime = row[8]
                        receive_time = row[9]
                        patient_name = row[10]
                        patient_age = row[11]
                        patient_gender = row[12]
                        analysis_mode = row[13]
                        mode_name_zh = row[14]
                        mode_desc_zh = row[15]
                        mode_typical_fields_json = row[16]
                        image_filename = row[17]
                        image_path = row[18]
                        image_size_bytes = row[19]
                        image_data_length_hex = row[20]
                        image_format = row[21]
                        image_saved_flag = row[22]
                        raw_payload = row[23]
                        created_at = row[24]
                        updated_at = row[25]
                        status = row[26]
                        hex_data = row[27]
                        base64_data = row[28]
                        
                        # 获取检测结果数据
                        cursor.execute('''
                            SELECT analyte_code, value_num, unit, raw_value
                            FROM lis_sample_results
                            WHERE sample_id = ?
                            ORDER BY analyte_code
                        ''', (sample_id,))
                        
                        results_data = {}
                        for result_row in cursor.fetchall():
                            analyte_code = result_row[0]
                            value_num = result_row[1]
                            unit = result_row[2]
                            raw_value = result_row[3]
                            results_data[analyte_code] = raw_value
                        
                        # 获取峰图数据
                        cursor.execute('''
                            SELECT peak_index, time_sec, area
                            FROM lis_sample_peaks
                            WHERE sample_id = ?
                            ORDER BY peak_index
                        ''', (sample_id,))
                        
                        time_data = []
                        area_data = []
                        peak_rows = cursor.fetchall()
                        
                        if peak_rows:
                            # 获取最大峰索引
                            max_peak_index = max(row[0] for row in peak_rows)
                            
                            # 初始化数组
                            time_data = ["00.00"] * max_peak_index
                            area_data = ["00.00"] * max_peak_index
                            
                            # 填充实际数据
                            for peak_row in peak_rows:
                                peak_index = peak_row[0]
                                time_sec = peak_row[1]
                                area = peak_row[2]
                                
                                if time_sec is not None:
                                    time_data[peak_index - 1] = f"{time_sec:.2f}"
                                if area is not None:
                                    area_data[peak_index - 1] = f"{area:.4f}"
                        
                        # 创建SampleInfo对象
                        sample_info = SampleInfo(
                            sample_id=sample_uid,
                            sample_number=str(sample_no),
                            sample_uid=sample_uid,  # 设置仪器检测编号
                            patient_name=patient_name or "",
                            patient_age=patient_age or 0,
                            patient_gender=patient_gender or "",
                            datetime=analysis_datetime,
                            sample_type=sample_type,
                            sample_type_name="",
                            sample_category="",
                            report_id="",
                            position=position_no or ""
                        )
                        
                        # 创建Results对象
                        results = Results(
                            HbA1a=results_data.get('HbA1a', ''),
                            HbA1b=results_data.get('HbA1b', ''),
                            HbF=results_data.get('HbF', ''),
                            LA1c=results_data.get('LA1c', ''),
                            HbA1c=results_data.get('HbA1c', ''),
                            HbA0=results_data.get('HbA0', ''),
                            eAG=results_data.get('eAG', ''),
                            A2=results_data.get('A2', ''),
                            HbE=results_data.get('HbE', ''),
                            HbD=results_data.get('HbD', ''),
                            HbS=results_data.get('HbS', ''),
                            HbC=results_data.get('HbC', ''),
                            p3=results_data.get('p3', ''),
                            v_win=results_data.get('v_win', ''),
                            TIME=time_data,
                            AREA=area_data,
                            TIME_DETAILS={},
                            AREA_DETAILS={},
                            other_results=results_data
                        )
                        
                        # 创建ImageInfo对象
                        image = ImageInfo(
                            filename=image_filename or "",
                            size=image_size_bytes or 0,
                            path=image_path or "",
                            saved=bool(image_saved_flag),
                            hex_data=hex_data or "",
                            base64_data=base64_data or "",
                            error=""
                        )
                        
                        # 创建ModeInfo对象
                        typical_fields = []
                        if mode_typical_fields_json:
                            try:
                                typical_fields = json.loads(mode_typical_fields_json)
                            except:
                                typical_fields = []
                        
                        mode_info = ModeInfo(
                            name=mode_name_zh or "",
                            description=mode_desc_zh or "",
                            mode_name=mode_name_zh or "",
                            mode_description=mode_desc_zh or "",
                            test_count=len(typical_fields),
                            test_items=typical_fields,
                            typical_fields=typical_fields
                        )
                        
                        # 创建Sample对象
                        sample = Sample(
                            id=sample_id,
                            protocol=protocol,
                            machine_model=machine_model,
                            machine_id=machine_id,
                            sample_info=sample_info,
                            results=results,
                            image=image,
                            analysis_mode=analysis_mode,
                            mode_info=mode_info,
                            raw=raw_payload,
                            receive_time=datetime.fromisoformat(receive_time) if receive_time else datetime.now(),
                            status=self._parse_sample_status(status),
                            created_time=datetime.fromisoformat(created_at) if created_at else datetime.now(),
                            updated_time=datetime.fromisoformat(updated_at) if updated_at else datetime.now()
                        )
                        samples.append(sample)
                        
                    except Exception as e:
                        logger.error(f"解析样本数据失败: {e}, row: {row}")
                        continue
                
                logger.info(f"高级搜索找到 {len(samples)} 个样本")
                return samples
                
        except Exception as e:
            logger.error(f"高级搜索样本失败: {e}")
            return []
    
    def add_operation_log(self, log: OperationLog) -> bool:
        """添加操作日志"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    INSERT INTO operation_logs (operator, operation_type, operation_content, ip_address)
                    VALUES (?, ?, ?, ?)
                ''', (log.operator, log.operation_type, log.operation_content, log.ip_address))
                conn.commit()
                return True
        except Exception as e:
            logger.error(f"添加操作日志失败: {e}")
            return False
    
    def get_operation_logs(self, filters: Dict = None) -> List[OperationLog]:
        """获取操作日志"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            query = "SELECT * FROM operation_logs"
            params = []
            
            if filters:
                conditions = []
                for key, value in filters.items():
                    if value:
                        if key == "date_range":
                            conditions.append("operation_time BETWEEN ? AND ?")
                            params.extend(value)
                        else:
                            conditions.append(f"{key} LIKE ?")
                            params.append(f"%{value}%")
                
                if conditions:
                    query += " WHERE " + " AND ".join(conditions)
            
            query += " ORDER BY operation_time DESC"
            
            cursor.execute(query, params)
            rows = cursor.fetchall()
            
            logs = []
            for row in rows:
                log = OperationLog(
                    id=row[0],
                    operation_time=datetime.fromisoformat(row[1]),
                    operator=row[2],
                    operation_type=row[3],
                    operation_content=row[4],
                    ip_address=row[5] if row[5] else ""
                )
                logs.append(log)
            
            return logs
    
    def get_system_config(self, key: str) -> Optional[str]:
        """获取系统配置"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute("SELECT value FROM system_config WHERE key = ?", (key,))
                result = cursor.fetchone()
                return result[0] if result else None
        except Exception as e:
            logger.error(f"获取系统配置失败: {e}")
            return None
    
    def set_system_config(self, key: str, value: str) -> bool:
        """设置系统配置"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    INSERT OR REPLACE INTO system_config (key, value, updated_time)
                    VALUES (?, ?, CURRENT_TIMESTAMP)
                ''', (key, value))
                conn.commit()
                return True
        except Exception as e:
            logger.error(f"设置系统配置失败: {e}")
            return False
    
    def get_warning_ranges(self, filters: Dict = None) -> List:
        """获取警告区间列表"""
        from src.models.models import WarningRange
        
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                query = "SELECT * FROM test_warning_ranges"
                params = []
                
                if filters:
                    conditions = []
                    for key, value in filters.items():
                        if value:
                            conditions.append(f"{key} LIKE ?")
                            params.append(f"%{value}%")
                    
                    if conditions:
                        query += " WHERE " + " AND ".join(conditions)
                
                query += " ORDER BY created_time DESC"
                
                cursor.execute(query, params)
                rows = cursor.fetchall()
                
                warning_ranges = []
                for row in rows:
                    warning_range = WarningRange(
                        test_name=row[1],
                        min_value=row[2],
                        max_value=row[3],
                        unit=row[4],
                        warning_color=row[5],
                        created_time=datetime.fromisoformat(row[6]) if row[6] else datetime.now()
                    )
                    warning_ranges.append(warning_range)
                
                return warning_ranges
                
        except Exception as e:
            logger.error(f"获取警告区间失败: {e}")
            return []
    
    def add_warning_range(self, warning_range) -> bool:
        """添加警告区间"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    INSERT INTO test_warning_ranges (test_name, min_value, max_value, unit, warning_color)
                    VALUES (?, ?, ?, ?, ?)
                ''', (
                    warning_range.test_name,
                    warning_range.min_value,
                    warning_range.max_value,
                    warning_range.unit,
                    warning_range.warning_color
                ))
                conn.commit()
                return True
        except Exception as e:
            logger.error(f"添加警告区间失败: {e}")
            return False
    
    def update_warning_range(self, warning_range) -> bool:
        """更新警告区间"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    UPDATE test_warning_ranges 
                    SET min_value = ?, max_value = ?, unit = ?, warning_color = ?
                    WHERE test_name = ?
                ''', (
                    warning_range.min_value,
                    warning_range.max_value,
                    warning_range.unit,
                    warning_range.warning_color,
                    warning_range.test_name
                ))
                conn.commit()
                return True
        except Exception as e:
            logger.error(f"更新警告区间失败: {e}")
            return False
    
    def delete_warning_range(self, test_name: str) -> bool:
        """删除警告区间"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('DELETE FROM test_warning_ranges WHERE test_name = ?', (test_name,))
                conn.commit()
                return True
        except Exception as e:
            logger.error(f"删除警告区间失败: {e}")
            return False
    
    def get_encryption_key(self, key_name: str) -> Optional[Dict]:
        """获取加密密钥"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT key_name, public_key, private_key, key_type, key_size, description, created_time
                    FROM encryption_keys 
                    WHERE key_name = ?
                ''', (key_name,))
                result = cursor.fetchone()
                
                if result:
                    return {
                        "key_name": result[0],
                        "public_key": result[1],
                        "private_key": result[2],
                        "key_type": result[3],
                        "key_size": result[4],
                        "description": result[5],
                        "created_time": result[6]
                    }
                return None
        except Exception as e:
            logger.error(f"获取加密密钥失败: {e}")
            return None
    
    def store_encryption_key(self, key_name: str, public_key: str, private_key: str, 
                           key_type: str, key_size: int, description: str = "") -> bool:
        """存储加密密钥"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    INSERT OR REPLACE INTO encryption_keys 
                    (key_name, public_key, private_key, key_type, key_size, description, created_time)
                    VALUES (?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
                ''', (key_name, public_key, private_key, key_type, key_size, description))
                conn.commit()
                return True
        except Exception as e:
            logger.error(f"存储加密密钥失败: {e}")
            return False
    
    def delete_encryption_key(self, key_name: str) -> bool:
        """删除加密密钥"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('DELETE FROM encryption_keys WHERE key_name = ?', (key_name,))
                conn.commit()
                return True
        except Exception as e:
            logger.error(f"删除加密密钥失败: {e}")
            return False
    
    def list_encryption_keys(self) -> List[Dict]:
        """列出所有加密密钥"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT key_name, key_type, key_size, description, created_time
                    FROM encryption_keys
                    ORDER BY created_time DESC
                ''')
                results = cursor.fetchall()
                
                keys = []
                for result in results:
                    keys.append({
                        "key_name": result[0],
                        "key_type": result[1],
                        "key_size": result[2],
                        "description": result[3],
                        "created_time": result[4]
                    })
                return keys
        except Exception as e:
            logger.error(f"列出加密密钥失败: {e}")
            return []
    
    # 用户管理相关方法
    def get_all_users(self) -> List[User]:
        """获取所有用户"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT id, username, password_hash, role, created_time, last_login
                    FROM users
                    ORDER BY created_time DESC
                ''')
                results = cursor.fetchall()
                
                users = []
                for result in results:
                    users.append(User(
                        id=result[0],
                        username=result[1],
                        password_hash=result[2],
                        role=UserRole(result[3]),
                        created_time=datetime.fromisoformat(result[4]),
                        last_login=datetime.fromisoformat(result[5]) if result[5] else None
                    ))
                return users
        except Exception as e:
            logger.error(f"获取所有用户失败: {e}")
            return []
    
    def get_user_by_id(self, user_id: int) -> Optional[User]:
        """根据ID获取用户"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT id, username, password_hash, role, created_time, last_login
                    FROM users
                    WHERE id = ?
                ''', (user_id,))
                result = cursor.fetchone()
                
                if result:
                    return User(
                        id=result[0],
                        username=result[1],
                        password_hash=result[2],
                        role=UserRole(result[3]),
                        created_time=datetime.fromisoformat(result[4]),
                        last_login=datetime.fromisoformat(result[5]) if result[5] else None
                    )
                return None
        except Exception as e:
            logger.error(f"根据ID获取用户失败: {e}")
            return None
    
    def get_user_by_username(self, username: str) -> Optional[User]:
        """根据用户名获取用户"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT id, username, password_hash, role, created_time, last_login
                    FROM users
                    WHERE username = ?
                ''', (username,))
                result = cursor.fetchone()
                
                if result:
                    return User(
                        id=result[0],
                        username=result[1],
                        password_hash=result[2],
                        role=UserRole(result[3]),
                        created_time=datetime.fromisoformat(result[4]),
                        last_login=datetime.fromisoformat(result[5]) if result[5] else None
                    )
                return None
        except Exception as e:
            logger.error(f"根据用户名获取用户失败: {e}")
            return None
    
    def add_user(self, username: str, password: str, role: UserRole) -> bool:
        """添加新用户"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    INSERT INTO users (username, password_hash, role, created_time)
                    VALUES (?, ?, ?, CURRENT_TIMESTAMP)
                ''', (username, self._hash_password(password), role.value))
                conn.commit()
                return True
        except sqlite3.IntegrityError:
            logger.error(f"用户 {username} 已存在")
            return False
        except Exception as e:
            logger.error(f"添加用户失败: {e}")
            return False
    
    def update_user(self, user_id: int, username: str, role: UserRole, password: str = None) -> bool:
        """更新用户信息"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                if password:
                    # 如果提供了新密码，更新密码
                    cursor.execute('''
                        UPDATE users 
                        SET username = ?, password_hash = ?, role = ?
                        WHERE id = ?
                    ''', (username, self._hash_password(password), role.value, user_id))
                else:
                    # 只更新用户名和角色
                    cursor.execute('''
                        UPDATE users 
                        SET username = ?, role = ?
                        WHERE id = ?
                    ''', (username, role.value, user_id))
                
                conn.commit()
                return True
        except sqlite3.IntegrityError:
            logger.error(f"用户名 {username} 已存在")
            return False
        except Exception as e:
            logger.error(f"更新用户失败: {e}")
            return False
    
    def delete_user(self, user_id: int) -> bool:
        """删除用户"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 检查是否为最后一个管理员
                cursor.execute('SELECT COUNT(*) FROM users WHERE role = ?', (UserRole.ADMIN.value,))
                admin_count = cursor.fetchone()[0]
                
                cursor.execute('SELECT role FROM users WHERE id = ?', (user_id,))
                user_role = cursor.fetchone()
                
                if user_role and user_role[0] == UserRole.ADMIN.value and admin_count <= 1:
                    logger.error("无法删除最后一个管理员用户")
                    return False
                
                cursor.execute('DELETE FROM users WHERE id = ?', (user_id,))
                conn.commit()
                return True
        except Exception as e:
            logger.error(f"删除用户失败: {e}")
            return False
    
    def delete_users(self, user_ids: List[int]) -> bool:
        """批量删除用户"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 检查是否包含管理员
                cursor.execute('SELECT COUNT(*) FROM users WHERE role = ?', (UserRole.ADMIN.value,))
                total_admin_count = cursor.fetchone()[0]
                
                placeholders = ','.join('?' * len(user_ids))
                cursor.execute(f'SELECT COUNT(*) FROM users WHERE id IN ({placeholders}) AND role = ?', 
                             user_ids + [UserRole.ADMIN.value])
                delete_admin_count = cursor.fetchone()[0]
                
                if total_admin_count - delete_admin_count < 1:
                    logger.error("无法删除所有管理员用户")
                    return False
                
                # 批量删除
                placeholders = ','.join('?' * len(user_ids))
                cursor.execute(f'DELETE FROM users WHERE id IN ({placeholders})', user_ids)
                conn.commit()
                return True
        except Exception as e:
            logger.error(f"批量删除用户失败: {e}")
            return False
    
    def change_user_password(self, user_id: int, new_password: str) -> bool:
        """修改用户密码"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    UPDATE users 
                    SET password_hash = ?
                    WHERE id = ?
                ''', (self._hash_password(new_password), user_id))
                conn.commit()
                return True
        except Exception as e:
            logger.error(f"修改用户密码失败: {e}")
            return False
    
    def search_users(self, keyword: str) -> List[User]:
        """搜索用户"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT id, username, password_hash, role, created_time, last_login
                    FROM users
                    WHERE username LIKE ? OR role LIKE ?
                    ORDER BY created_time DESC
                ''', (f'%{keyword}%', f'%{keyword}%'))
                results = cursor.fetchall()
                
                users = []
                for result in results:
                    users.append(User(
                        id=result[0],
                        username=result[1],
                        password_hash=result[2],
                        role=UserRole(result[3]),
                        created_time=datetime.fromisoformat(result[4]),
                        last_login=datetime.fromisoformat(result[5]) if result[5] else None
                    ))
                return users
        except Exception as e:
            logger.error(f"搜索用户失败: {e}")
            return []
    
    def get_sample_by_id(self, sample_id: str) -> Optional[Sample]:
        """根据样本ID获取单个样本"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 获取主表数据
                cursor.execute('''
                    SELECT s.*, si.hex_data, si.base64_data
                    FROM lis_samples s
                    LEFT JOIN sample_images si ON s.id = si.sample_id
                    WHERE s.id = ?
                ''', (sample_id,))
                
                row = cursor.fetchone()
                if not row:
                    logger.warning(f"未找到样本ID: {sample_id}")
                    return None
                
                # 解析主表数据
                sample_id = row[0]
                protocol = row[1]
                machine_model = row[2]
                machine_id = row[3]
                sample_no = row[4]
                sample_uid = row[5]
                position_no = row[6]
                sample_type = row[7]
                analysis_datetime = row[8]
                receive_time = row[9]
                patient_name = row[10]
                patient_age = row[11]
                patient_gender = row[12]
                analysis_mode = row[13]
                mode_name_zh = row[14]
                mode_desc_zh = row[15]
                mode_typical_fields_json = row[16]
                image_filename = row[17]
                image_path = row[18]
                image_size_bytes = row[19]
                image_data_length_hex = row[20]
                image_format = row[21]
                image_saved_flag = row[22]
                raw_payload = row[23]
                created_at = row[24]
                updated_at = row[25]
                status = row[26]
                hex_data = row[27]
                base64_data = row[28]
                
                # 获取检测结果数据
                cursor.execute('''
                    SELECT analyte_code, value_num, unit, raw_value
                    FROM lis_sample_results
                    WHERE sample_id = ?
                    ORDER BY analyte_code
                ''', (sample_id,))
                
                results_data = {}
                for result_row in cursor.fetchall():
                    analyte_code = result_row[0]
                    value_num = result_row[1]
                    unit = result_row[2]
                    raw_value = result_row[3]
                    results_data[analyte_code] = raw_value
                
                # 获取峰图数据
                cursor.execute('''
                    SELECT peak_index, time_sec, area
                    FROM lis_sample_peaks
                    WHERE sample_id = ?
                    ORDER BY peak_index
                ''', (sample_id,))
                
                time_data = []
                area_data = []
                peak_rows = cursor.fetchall()
                
                if peak_rows:
                    # 获取最大峰索引
                    max_peak_index = max(row[0] for row in peak_rows)
                    
                    # 初始化数组
                    time_data = ["00.00"] * max_peak_index
                    area_data = ["00.00"] * max_peak_index
                    
                    # 填充实际数据
                    for peak_row in peak_rows:
                        peak_index = peak_row[0]
                        time_sec = peak_row[1]
                        area = peak_row[2]
                        
                        if time_sec is not None:
                            time_data[peak_index - 1] = f"{time_sec:.2f}"
                        if area is not None:
                            area_data[peak_index - 1] = f"{area:.4f}"
                
                # 创建SampleInfo对象
                sample_info = SampleInfo(
                    sample_id=sample_id,
                    sample_number=str(sample_no),
                    sample_uid=sample_uid,  # 设置仪器检测编号
                    patient_name=patient_name or "",
                    patient_age=patient_age or 0,
                    patient_gender=patient_gender or "",
                    datetime=analysis_datetime,
                    sample_type=sample_type,
                    sample_type_name=sample_type,
                    sample_category="常规",
                    report_id=sample_id,
                    position=position_no
                )
                
                # 创建Results对象
                results = Results(
                    HbA1a=results_data.get('HbA1a', '0.00'),
                    HbA1b=results_data.get('HbA1b', '0.00'),
                    HbF=results_data.get('HbF', '0.00'),
                    LA1c=results_data.get('LA1c', '0.00'),
                    HbA1c=results_data.get('HbA1c', '0.00'),
                    HbA0=results_data.get('HbA0', '0.00'),
                    eAG=results_data.get('eAG', '0.00'),
                    A2=results_data.get('A2', '0.00'),
                    HbE=results_data.get('HbE', '0.00'),
                    HbD=results_data.get('HbD', '0.00'),
                    HbS=results_data.get('HbS', '0.00'),
                    HbC=results_data.get('HbC', '0.00'),
                    p3=results_data.get('p3', '0.00'),
                    v_win=results_data.get('v_win', '0.00'),
                    TIME=time_data,
                    AREA=area_data
                )
                
                # 创建ImageInfo对象
                image_info = ImageInfo(
                    filename=image_filename or "",
                    path=image_path or "",
                    size=image_size_bytes or 0,
                    saved=bool(image_saved_flag),
                    hex_data=hex_data or "",
                    base64_data=base64_data or ""
                )
                
                # 创建ModeInfo对象
                mode_info = ModeInfo(
                    name=mode_name_zh or analysis_mode,
                    description=mode_desc_zh or "",
                    mode_name=mode_name_zh or analysis_mode,
                    mode_description=mode_desc_zh or "",
                    typical_fields=json.loads(mode_typical_fields_json) if mode_typical_fields_json else []
                )
                
                # 创建Sample对象
                sample = Sample(
                    id=sample_id,
                    protocol=protocol,
                    machine_model=machine_model,
                    machine_id=machine_id,
                    sample_info=sample_info,
                    results=results,
                    image=image_info,
                    analysis_mode=analysis_mode,
                    mode_info=mode_info,
                    raw=raw_payload,
                    receive_time=datetime.fromisoformat(receive_time) if receive_time else datetime.now(),
                    status=self._parse_sample_status(status),
                    created_time=datetime.fromisoformat(created_at) if created_at else datetime.now(),
                    updated_time=datetime.fromisoformat(updated_at) if updated_at else datetime.now()
                )
                
                logger.info(f"成功获取样本: {sample.sample_info.sample_number} - {sample.sample_info.patient_name}")
                return sample
                
        except Exception as e:
            logger.error(f"根据ID获取样本失败: {e}")
            return None 
    
    def get_samples_paginated(self, page: int = 1, page_size: int = 20, 
                              sort_field: str = "updated_at", sort_order: str = "DESC",
                              filters: Dict[str, Any] = None) -> Dict[str, Any]:
        """
        分页获取样本数据 - 真正的数据库分页，避免一次性加载所有数据
        
        Args:
            page: 页码（从1开始）
            page_size: 每页大小
            sort_field: 排序字段
            sort_order: 排序方式（ASC/DESC）
            filters: 过滤条件
            
        Returns:
            Dict包含:
            - samples: 当前页样本列表
            - total_count: 总记录数
            - total_pages: 总页数
            - current_page: 当前页
            - page_size: 每页大小
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 构建基础查询
                base_query = '''
                    FROM lis_samples s
                    LEFT JOIN sample_images si ON s.id = si.sample_id
                '''
                
                # 构建WHERE子句
                where_clause = ""
                params = []
                
                if filters:
                    conditions = []
                    for key, value in filters.items():
                        if value is not None and value != "":
                            if key == "patient_name":
                                conditions.append("s.patient_name LIKE ?")
                                params.append(f"%{value}%")
                            elif key == "sample_no":
                                conditions.append("s.sample_no = ?")
                                params.append(value)
                            elif key == "status":
                                conditions.append("s.status = ?")
                                params.append(value)
                            elif key == "machine_model":
                                conditions.append("s.machine_model LIKE ?")
                                params.append(f"%{value}%")
                            elif key == "date_from":
                                conditions.append("s.receive_time >= ?")
                                params.append(value)
                            elif key == "date_to":
                                conditions.append("s.receive_time <= ?")
                                params.append(value)
                    
                    if conditions:
                        where_clause = "WHERE " + " AND ".join(conditions)
                
                # 构建排序子句
                sort_mapping = {
                    "updated_at": "s.updated_at",
                    "receive_time": "s.receive_time",
                    "created_at": "s.created_at",
                    "sample_no": "s.sample_no",
                    "patient_name": "s.patient_name",
                    "patient_age": "s.patient_age",
                    "patient_gender": "s.patient_gender",
                    "machine_model": "s.machine_model",
                    "machine_id": "s.machine_id",
                    "position_no": "s.position_no",
                    "status": "s.status",
                    "analysis_mode": "s.analysis_mode",
                    "protocol": "s.protocol"
                }
                
                sort_column = sort_mapping.get(sort_field, "s.updated_at")
                order_clause = f"ORDER BY {sort_column} {sort_order}"
                
                # 获取总记录数
                count_query = f"SELECT COUNT(*) {base_query} {where_clause}"
                logger.debug(f"执行计数查询: {count_query}")
                logger.debug(f"查询参数: {params}")
                cursor.execute(count_query, params)
                total_count = cursor.fetchone()[0]
                logger.debug(f"总记录数: {total_count}")
                
                # 计算分页信息
                total_pages = (total_count + page_size - 1) // page_size
                offset = (page - 1) * page_size
                
                # 获取当前页数据
                data_query = f"""
                    SELECT s.*, si.hex_data, si.base64_data
                    {base_query}
                    {where_clause}
                    {order_clause}
                    LIMIT ? OFFSET ?
                """
                
                logger.debug(f"执行数据查询: {data_query}")
                logger.debug(f"查询参数: {params + [page_size, offset]}")
                cursor.execute(data_query, params + [page_size, offset])
                rows = cursor.fetchall()
                logger.debug(f"查询结果行数: {len(rows)}")
                
                # 构建样本对象
                samples = []
                for row in rows:
                    try:
                        sample = self._build_sample_from_row(cursor, row)
                        if sample:
                            samples.append(sample)
                    except Exception as e:
                        logger.warning(f"构建样本对象失败: {e}")
                        continue
                
                return {
                    "samples": samples,
                    "total_count": total_count,
                    "total_pages": total_pages,
                    "current_page": page,
                    "page_size": page_size,
                    "has_previous": page > 1,
                    "has_next": page < total_pages
                }
                
        except Exception as e:
            logger.error(f"分页获取样本失败: {e}")
            return {
                "samples": [],
                "total_count": 0,
                "total_pages": 0,
                "current_page": page,
                "page_size": page_size,
                "has_previous": False,
                "has_next": False
            }
    
    def get_samples_count(self, filters: Dict[str, Any] = None) -> int:
        """
        获取样本总数（用于分页计算）
        
        Args:
            filters: 过滤条件
            
        Returns:
            样本总数
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                base_query = "FROM lis_samples s"
                where_clause = ""
                params = []
                
                if filters:
                    conditions = []
                    for key, value in filters.items():
                        if value is not None and value != "":
                            if key == "patient_name":
                                conditions.append("s.patient_name LIKE ?")
                                params.append(f"%{value}%")
                            elif key == "sample_no":
                                conditions.append("s.sample_no = ?")
                                params.append(value)
                            elif key == "status":
                                conditions.append("s.status = ?")
                                params.append(value)
                            elif key == "machine_model":
                                conditions.append("s.machine_model LIKE ?")
                                params.append(f"%{value}%")
                            elif key == "date_from":
                                conditions.append("s.receive_time >= ?")
                                params.append(value)
                            elif key == "date_to":
                                conditions.append("s.receive_time <= ?")
                                params.append(value)
                    
                    if conditions:
                        where_clause = "WHERE " + " AND ".join(conditions)
                
                count_query = f"SELECT COUNT(*) {base_query} {where_clause}"
                cursor.execute(count_query, params)
                return cursor.fetchone()[0]
                
        except Exception as e:
            logger.error(f"获取样本总数失败: {e}")
            return 0
    
    def _build_sample_from_row(self, cursor, row) -> Optional[Sample]:
        """
        从数据库行数据构建Sample对象
        
        Args:
            cursor: 数据库游标
            row: 数据库行数据
            
        Returns:
            Sample对象或None
        """
        try:
            # 解析主表数据
            sample_id = row[0]
            protocol = row[1]
            machine_model = row[2]
            machine_id = row[3]
            sample_no = row[4]
            sample_uid = row[5]
            position_no = row[6]
            sample_type = row[7]
            analysis_datetime = row[8]
            receive_time = row[9]
            patient_name = row[10]
            patient_age = row[11]
            patient_gender = row[12]
            analysis_mode = row[13]
            mode_name_zh = row[14]
            mode_desc_zh = row[15]
            mode_typical_fields_json = row[16]
            image_filename = row[17]
            image_path = row[18]
            image_size_bytes = row[19]
            image_data_length_hex = row[20]
            image_format = row[21]
            image_saved_flag = row[22]
            raw_payload = row[23]
            created_at = row[24]
            updated_at = row[25]
            status = row[26]
            hex_data = row[27]
            base64_data = row[28]
            
            # 获取检测结果数据
            cursor.execute('''
                SELECT analyte_code, value_num, unit, raw_value
                FROM lis_sample_results
                WHERE sample_id = ?
                ORDER BY analyte_code
            ''', (sample_id,))
            
            results_data = {}
            for result_row in cursor.fetchall():
                analyte_code = result_row[0]
                value_num = result_row[1]
                unit = result_row[2]
                raw_value = result_row[3]
                results_data[analyte_code] = raw_value
            
            # 获取峰图数据
            cursor.execute('''
                SELECT peak_index, time_sec, area
                FROM lis_sample_peaks
                WHERE sample_id = ?
                ORDER BY peak_index
            ''', (sample_id,))
            
            time_data = []
            area_data = []
            peak_rows = cursor.fetchall()
            
            if peak_rows:
                # 获取最大峰索引
                max_peak_index = max(row[0] for row in peak_rows)
                
                # 初始化数组
                time_data = ["00.00"] * max_peak_index
                area_data = ["00.00"] * max_peak_index
                
                # 填充实际数据
                for peak_row in peak_rows:
                    peak_index = peak_row[0]
                    time_sec = peak_row[1]
                    area = peak_row[2]
                    
                    if time_sec is not None:
                        time_data[peak_index - 1] = f"{time_sec:.2f}"
                    if area is not None:
                        area_data[peak_index - 1] = f"{area:.4f}"
            
            # 构建Sample对象
            try:
                # 尝试导入Sample类
                from src.models.models import Sample, SampleInfo, Results, SampleStatus
                
                # 创建SampleInfo对象
                sample_info = SampleInfo(
                    sample_number=str(sample_no),
                    sample_uid=sample_uid,  # 设置仪器检测编号
                    patient_name=patient_name or "未知患者",
                    patient_age=patient_age or 0,
                    patient_gender=patient_gender or "未知",
                    position=position_no
                )
                
                # 创建Results对象
                results = Results()
                # 设置检测结果数据
                for analyte_code, raw_value in results_data.items():
                    setattr(results, analyte_code, raw_value)
                
                # 设置峰图数据
                results.TIME = time_data
                results.AREA = area_data
                
                # 处理状态字段 - 将字符串转换为枚举值
                try:
                    if status:
                        # 尝试将字符串状态转换为枚举值
                        if status == 'PENDING':
                            sample_status = SampleStatus.PENDING
                        elif status == 'PROCESSED':
                            sample_status = SampleStatus.PROCESSED
                        elif status == 'EXPORTED':
                            sample_status = SampleStatus.EXPORTED
                        elif status == 'SENT':
                            sample_status = SampleStatus.SENT
                        else:
                            sample_status = SampleStatus.PENDING
                    else:
                        sample_status = SampleStatus.PENDING
                except Exception:
                    sample_status = SampleStatus.PENDING
                
                # 创建Sample对象
                sample = Sample(
                    id=sample_id,
                    sample_info=sample_info,
                    machine_model=machine_model,
                    machine_id=machine_id,
                    receive_time=receive_time,
                    created_time=created_at,
                    status=sample_status,
                    results=results,
                    protocol=protocol,
                    analysis_mode=analysis_mode
                )
                
                return sample
                
            except Exception as e:
                logger.warning(f"创建Sample对象失败，使用简化对象: {e}")
                # 如果创建失败，返回一个简化的字典对象
                return {
                    "id": sample_id,
                    "sample_info": {
                        "sample_number": str(sample_no),
                        "patient_name": patient_name or "未知患者",
                        "patient_age": patient_age or 0,
                        "patient_gender": patient_gender or "未知",
                        "position": position_no
                    },
                    "machine_model": machine_model,
                    "machine_id": machine_id,
                    "receive_time": receive_time,
                    "created_time": created_at,
                    "status": status,
                    "protocol": protocol,
                    "analysis_mode": analysis_mode
                }
            
        except Exception as e:
            logger.error(f"构建样本对象失败: {e}")
            return None